package com.bookshop.dao;

import com.bookshop.bean.BookBean;
import com.bookshop.bean.PageBean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class BookDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    //插入图书
    public int insertBook(BookBean book){
        String sql = "INSERT INTO book (book_name,type_id,author,publish,depict,price,storages) "+
                "VALUES(?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,book.getBookName(),book.getTypeId(),book.getAuthor(),book.getPublish(),book.getDepict()
        ,book.getPrice(),book.getStorages());
    }

    /**
     * 分页查询图书
     * @param page
     * @return
     */
    public List<BookBean> selectBooksPage(PageBean page){
        String sql="SELECT book_id,book_name,book.type_id,author,publish,depict,price,storages FROM book LIMIT ?,?";

        List<BookBean> books = new ArrayList<>();
        books = jdbcTemplate.query(sql, new RowMapper<BookBean>() {
            @Override
            public BookBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                BookBean book = new BookBean();
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setTypeId(rs.getInt("type_id"));
                book.setAuthor(rs.getString("author"));
                book.setPublish(rs.getString("publish"));
                book.setDepict(rs.getString("depict"));
                book.setPrice(rs.getDouble("price"));
                book.setStorages(rs.getInt("storages"));
                return book;
            }
    },(page.getPageNow()-1)*page.getPageSize(),page.getPageSize());
        return books;
    }

    //图书总行数
    public int getRowCount(){
        String sql = "SELECT COUNT(book_id) FROM book";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    /**
     * 删除图书
     * @param id
     * @return
     */
    public int deleteBook(Integer id){
        String sql = "DELETE FROM book WHERE book_id = ?";
        return jdbcTemplate.update(sql,id);
    }

    //修改图书
    public int updateBook(BookBean book){
        String sql = "UPDATE book SET book_name = ?,type_id = ?,author = ?,publish = ?,depict = ?,"+
                "price = ?,storages = ? WHERE book_id = ?";
        return jdbcTemplate.update(sql,book.getBookName(),book.getTypeId(),book.getAuthor(),book.getPublish(),
                book.getDepict(),book.getPrice(),book.getStorages(),book.getBookId());
    }
}
